﻿Imports System.Data.SqlClient
Public Class FrmHoanTraKiemke
    Dim tbl As DataTable
    Dim dbAdapter As SqlDataAdapter
    Dim cmd As SqlCommand
    Dim tennhap As String
    Dim maHSMuon As String
    Private Sub NapPhong()
        cmd = New SqlCommand(" select * from DMPhong", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxPhong.DisplayMember = "Tenphong"
        ComboBoxPhong.ValueMember = "maPhongID"
        ComboBoxPhong.DataSource = tbl

    End Sub
    Private Sub NapNgan()
        cmd = New SqlCommand(" select * from DMNgan", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxNgan.DisplayMember = "TenNgan"
        ComboBoxNgan.ValueMember = "maNganID"
        ComboBoxNgan.DataSource = tbl

    End Sub
    Private Sub NapKe()
        cmd = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKe.DisplayMember = "TenKe"
        ComboBoxKe.ValueMember = "maKeID"
        ComboBoxKe.DataSource = tbl

    End Sub
    Private Sub NapKho()
        cmd = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKho.DisplayMember = "TenKho"
        ComboBoxKho.ValueMember = "maKhoID"
        ComboBoxKho.DataSource = tbl

    End Sub
    Private Sub Naphoso()
        cmd = New SqlCommand(" select * from DMHosokiemke", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHoso.DisplayMember = "Tenloaihoso"
        ComboBoxHoso.ValueMember = "MaloaiID"
        ComboBoxHoso.DataSource = tbl

    End Sub
    Private Sub NapTaiLieuKK()
        cmd = New SqlCommand(" select * from DMTaiLieuKK", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxTaiLieuKK.DisplayMember = "TenTaiLieuKK"
        ComboBoxTaiLieuKK.ValueMember = "MaTaiLieuKK"
        ComboBoxTaiLieuKK.DataSource = tbl

    End Sub
    Private Sub NapDonViHuyen()
        cmd = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHuyen.DisplayMember = "T_huyen"
        ComboBoxHuyen.ValueMember = "maH"
        ComboBoxHuyen.DataSource = tbl
    End Sub
    Private Sub NapHinhThuc()
        cmd = New SqlCommand(" select * from DMHinhThuc", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHinhThuc.DisplayMember = "TenHinhThuc"
        ComboBoxHinhThuc.ValueMember = "maHinhThucID"
        ComboBoxHinhThuc.DataSource = tbl

    End Sub
    Private Sub NapThoiHanBaoQuan()
        cmd = New SqlCommand(" select * from DMThoiHanBaoQuan", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxThoiHanBQ.DisplayMember = "ThoiHanBaoQuan"
        ComboBoxThoiHanBQ.ValueMember = "MaDMThoiHan"
        ComboBoxThoiHanBQ.DataSource = tbl

    End Sub
    Private Sub FrmNhapKiemke_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NapKho()
        NapKe()
        NapNgan()
        NapPhong()
        Naphoso()
        NapTaiLieuKK()
        NapDonViHuyen()
        NapHinhThuc()
        NapThoiHanBaoQuan()


    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs)
        ' load ten xa
        cmd = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl

    End Sub
    Private Sub TimKiem()
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        cmd = New SqlCommand("SELECT hskk.NgayNhapKho, hskk.NamThucHien, dmtlkk.TenTaiLieuKK, dmhskk.Tenloaihoso, dvhc.TenCapDVHC, thbq.ThoiHanBaoQuan, kho.TenKho, ke.TenKe, ngan.TenNgan, phong.TenPhong, tinhtrang.TenTinhTrang, huyen.T_Huyen, xa.TenXa, hskk.MahosoID, cctt.MahsmuonID " &
                                "FROM CCTT_HOSOKIEMKE_THONGKE cctt, HOSOKIEMKE hskk, DMTailieuKK dmtlkk, DMHosokiemke dmhskk, CAPDONVIHANHCHINH dvhc, DMThoiHanBaoQuan thbq, TENXA xa, TENHUYEN huyen, DMKho kho, DMKe ke, DMNgan ngan, DMPhong phong, DMTinhTrang tinhtrang " &
                                "WHERE cctt.SoHoSo = hskk.MahosoID AND hskk.CapDVHCID = dvhc.CapDVHCID and hskk.MaLoaiHSID = dmtlkk.MaTaiLieuKK AND hskk.TenQuyen = dmhskk.MaloaiID AND hskk.MaTinhTrangId = tinhtrang.MaTinhTrangID AND  hskk.MaDMThoiHanID = thbq.MaDMThoiHan And hskk.MaxaId = xa.MaXa And hskk.MaHuyenID = huyen.maH And hskk.MaKhoId = kho.maKhoID And hskk.MaKeId = ke.MaKeID AND hskk.MaPhongId = phong.MaPhongID and hskk.MaNganId = ngan.MaNganID " &
                                "AND cctt.Tinhtrangid = 2 " &
                                "AND hskk.NamThucHien LIKE '%" & TextBoxNamLap.Text & "%'", ob_cnn)
        tbl = New DataTable()
        tbl.Load(cmd.ExecuteReader())
        DataGridView_hienthi.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub

    Private Sub LoadHoSoNguoiMuon(ByVal nguoimuon As String)
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        cmd = New SqlCommand("SELECT hskk.NgayNhapKho, hskk.NamThucHien, dmtlkk.TenTaiLieuKK, dmhskk.Tenloaihoso, dvhc.TenCapDVHC, thbq.ThoiHanBaoQuan, kho.TenKho, ke.TenKe, ngan.TenNgan, phong.TenPhong, tinhtrang.TenTinhTrang, huyen.T_Huyen, xa.TenXa, hskk.MahosoID, cctt.MahsmuonID " &
                                "FROM CCTT_HOSOKIEMKE_THONGKE cctt, HOSOKIEMKE hskk, DMTailieuKK dmtlkk, DMHosokiemke dmhskk, CAPDONVIHANHCHINH dvhc, DMThoiHanBaoQuan thbq, TENXA xa, TENHUYEN huyen, DMKho kho, DMKe ke, DMNgan ngan, DMPhong phong, DMTinhTrang tinhtrang " &
                                "WHERE cctt.SoHoSo = hskk.MahosoID AND hskk.CapDVHCID = dvhc.CapDVHCID and hskk.MaLoaiHSID = dmtlkk.MaTaiLieuKK AND hskk.TenQuyen = dmhskk.MaloaiID AND hskk.MaTinhTrangId = tinhtrang.MaTinhTrangID AND  hskk.MaDMThoiHanID = thbq.MaDMThoiHan And hskk.MaxaId = xa.MaXa And hskk.MaHuyenID = huyen.maH And hskk.MaKhoId = kho.maKhoID And hskk.MaKeId = ke.MaKeID AND hskk.MaPhongId = phong.MaPhongID and hskk.MaNganId = ngan.MaNganID " &
                                "AND cctt.Tinhtrangid = 2 " &
                                "AND cctt.NguoiMuon LIKE @nguoimuon", ob_cnn)
        cmd.Parameters.AddWithValue("@nguoimuon", "%" & nguoimuon & "%")
        tbl = New DataTable()
        tbl.Load(cmd.ExecuteReader())
        DataGridView_hienthi.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Function KiemTraTenNguoiTra()
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        cmd = New SqlCommand("SELECT NguoiMuon FROM CCTT_HOSOKIEMKE_THONGKE WHERE SoHoSo = @sohoso AND Tinhtrangid = 2", ob_cnn)
        cmd.Parameters.AddWithValue("@sohoso", TextBoxSoHoSo.Text)
        tbl = New DataTable()
        tbl.Load(cmd.ExecuteReader())
        Dim dr As DataRow
        dr = tbl.Rows(0)
        Dim tenNguoiMuon As String
        tenNguoiMuon = dr("NguoiMuon").ToString()
        ob_cnn.Close()
        Return tenNguoiMuon
    End Function
    Private Sub SuaTinhTrangHoSo(ByVal tinhtrang As Integer)
        Try
            If (ob_cnn.State = ConnectionState.Open) Then
                ob_cnn.Close()
            End If
            ob_cnn.Open()
            Dim sqlString As String = ""
            sqlString = "UPDATE HOSOKIEMKE SET MaTinhTrangId = @matinhtrangid " &
                  "WHERE MahosoID = @sohoso"
            cmd = New SqlCommand(sqlString, ob_cnn)
            cmd.Parameters.AddWithValue("@matinhtrangid", tinhtrang)
            cmd.Parameters.AddWithValue("@sohoso", TextBoxSoHoSo.Text)

            cmd.ExecuteNonQuery()
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub SuaTinhTrangHoSoDangMuon()
        Try
            If (ob_cnn.State = ConnectionState.Open) Then
                ob_cnn.Close()
            End If
            ob_cnn.Open()
            Dim sqlString As String = ""
            sqlString = "UPDATE CCTT_HOSOKIEMKE_THONGKE SET Tinhtrangid = @matinhtrangid, NgayTra = @ngaytra WHERE MahsmuonID = @mahsmuon"
            cmd = New SqlCommand(sqlString, ob_cnn)
            cmd.Parameters.AddWithValue("@matinhtrangid", 1)
            cmd.Parameters.AddWithValue("@ngaytra", DateTime.Now)
            cmd.Parameters.AddWithValue("@mahsmuon", maHSMuon)

            cmd.ExecuteNonQuery()
            MessageBox.Show("Thao tác thành công !")
            ob_cnn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub ButtonCapnhat_Click(sender As Object, e As EventArgs) Handles ButtonCapnhat.Click
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        If TextBoxSoHoSo.Text = Nothing Then
            MessageBox.Show("Vui lòng chọn hồ sơ trả !")
            Return
        End If
        If TextBoxNguoiYeuCau.Text = Nothing Then
            MessageBox.Show("Vui lòng nhập tên người trả hồ sơ !")
            Return
        End If
        If KiemTraTenNguoiTra() <> TextBoxNguoiYeuCau.Text.Trim Then
            MessageBox.Show("Người yêu cầu không đúng !")
            Return
        End If
        SuaTinhTrangHoSo(1)
        SuaTinhTrangHoSoDangMuon()
        LoadHoSoNguoiMuon(TextBoxNguoiYeuCau.Text)
        LoadNguoiMuon()
        ob_cnn.Close()
        ButtonCapnhat.Enabled = False
    End Sub
    Private Sub NapVaoTexBox()
        ComboBoxHoso.DataBindings.Clear()
        ComboBoxHoso.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "Tenloaihoso")
        TextBoxNamLap.DataBindings.Clear()
        TextBoxNamLap.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "NamThucHien")
        TextBoxSoHoSo.DataBindings.Clear()
        TextBoxSoHoSo.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "MahosoID")
        DateTimePicker1.DataBindings.Clear()
        DateTimePicker1.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "NgayNhapKho")
        ComboBoxPhong.DataBindings.Clear()
        ComboBoxPhong.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenPhong")
        ComboBoxNgan.DataBindings.Clear()
        ComboBoxNgan.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenNgan")
        ComboBoxKe.DataBindings.Clear()
        ComboBoxKe.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenKe")
        ComboBoxKho.DataBindings.Clear()
        ComboBoxKho.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenKho")
        ComboBoxHuyen.DataBindings.Clear()
        ComboBoxHuyen.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "T_Huyen")
        ComboBoxXa.DataBindings.Clear()
        ComboBoxXa.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenXa")
        ComboBoxThoiHanBQ.DataBindings.Clear()
        ComboBoxThoiHanBQ.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "ThoiHanBaoQuan")
        If LayIDCapDVHC() = 1 Then
            rbTinh.Checked = True
            Return
        End If
        If LayIDCapDVHC() = 2 Then
            rbHuyen.Checked = True
            Return
        End If
        If LayIDCapDVHC() = 3 Then
            rbXa.Checked = True
            Return
        End If
    End Sub
    Private Function LayIDCapDVHC()
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        Dim sql As String = ""
        Dim id As Integer
        sql = "SELECT CapDVHCID FROM HOSOKIEMKE WHERE MahosoID = @mahosoid"
        cmd = New SqlCommand(sql, ob_cnn)
        cmd.Parameters.AddWithValue("mahosoid", TextBoxSoHoSo.Text)

        id = cmd.ExecuteScalar()
        ob_cnn.Close()
        Return id
    End Function

    Private Sub LoadNguoiMuon()
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        cmd = New SqlCommand("SELECT DISTINCT(NguoiMuon), DonVi, DiaChi, NgayMuon, HinhThucMuonId, MucDichMuon  FROM CCTT_HOSOKIEMKE_THONGKE WHERE Tinhtrangid = 2", ob_cnn)
        tbl = New DataTable()
        tbl.Load(cmd.ExecuteReader())
        DataGridViewNguoiMuon.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub DataGridViewNguoiMuon_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewNguoiMuon.CellClick
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        If e.RowIndex = -1 Then
            Return
        End If
        TextBoxNguoiYeuCau.DataBindings.Clear()
        TextBoxNguoiYeuCau.DataBindings.Add("Text", DataGridViewNguoiMuon.DataSource, "NguoiMuon")
        TextBoxDonViCT.DataBindings.Clear()
        TextBoxDonViCT.DataBindings.Add("Text", DataGridViewNguoiMuon.DataSource, "DonVi")
        TextBoxDiaChi.DataBindings.Clear()
        TextBoxDiaChi.DataBindings.Add("Text", DataGridViewNguoiMuon.DataSource, "DiaChi")
        TextBoxMucDichSuDung.DataBindings.Clear()
        TextBoxMucDichSuDung.DataBindings.Add("Text", DataGridViewNguoiMuon.DataSource, "MucDichMuon")
        DateTimePicker2.DataBindings.Clear()
        DateTimePicker2.DataBindings.Add("Text", DataGridViewNguoiMuon.DataSource, "NgayMuon")
        ComboBoxHinhThuc.DataBindings.Clear()
        ComboBoxHinhThuc.DataBindings.Add("SelectedValue", DataGridViewNguoiMuon.DataSource, "HinhThucMuonId")
        LoadHoSoNguoiMuon(DataGridViewNguoiMuon.Rows(e.RowIndex).Cells("NguoiMuon").Value)
        ob_cnn.Close()
    End Sub
    Private Sub LoadNguoiMuonTuGridviewHoSo()
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        cmd = New SqlCommand("SELECT NguoiMuon, DonVi, DiaChi, MucDichMuon, NgayMuon, HinhThucMuonId FROM CCTT_HOSOKIEMKE_THONGKE WHERE MahsmuonID = @mahsmuon", ob_cnn)
        cmd.Parameters.AddWithValue("@mahsmuon", maHSMuon)
        tbl = New DataTable()
        tbl.Load(cmd.ExecuteReader())
        Dim dr As DataRow
        dr = tbl.Rows(0)
        TextBoxNguoiYeuCau.Text = dr("NguoiMuon").ToString()
        TextBoxDonViCT.Text = dr("DonVi").ToString()
        TextBoxDiaChi.Text = dr("DiaChi").ToString()
        TextBoxMucDichSuDung.Text = dr("MucDichMuon").ToString()
        DateTimePicker2.Text = dr("NgayMuon").ToString()
        ComboBoxHinhThuc.SelectedValue = dr("HinhThucMuonId").ToString()
        ob_cnn.Close()
    End Sub
    Private Sub DataGridView_hienthi_CellClick_1(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView_hienthi.CellClick
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        If e.RowIndex = -1 Then
            Return
        End If
        NapVaoTexBox()
        maHSMuon = DataGridView_hienthi.Rows(e.RowIndex).Cells("MahsmuonID").Value
        LoadNguoiMuonTuGridviewHoSo()
        ob_cnn.Close()
        ButtonCapnhat.Enabled = True
    End Sub

    Private Sub TextBoxNguoiYeuCau_TextChanged(sender As Object, e As EventArgs) Handles TextBoxNguoiYeuCau.TextChanged
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        LoadHoSoNguoiMuon(TextBoxNguoiYeuCau.Text)
    End Sub

    Private Sub ButtonTimkiem_Click(sender As Object, e As EventArgs) Handles ButtonTimkiem.Click
        If (ob_cnn.State = ConnectionState.Open) Then
            ob_cnn.Close()
        End If
        ob_cnn.Open()
        TimKiem()
        LoadNguoiMuon()
        TextBoxNamLap.Text = ""
        ob_cnn.Close()
    End Sub
    Private Sub rbTinh_CheckedChanged(sender As Object, e As EventArgs) Handles rbTinh.CheckedChanged
        If rbTinh.Checked Then
            ComboBoxHuyen.Enabled = False
            ComboBoxXa.Enabled = False
        End If
    End Sub

    Private Sub rbHuyen_CheckedChanged(sender As Object, e As EventArgs) Handles rbHuyen.CheckedChanged
        If rbHuyen.Checked Then
            ComboBoxXa.Enabled = False
            ComboBoxHuyen.Enabled = True
        End If
    End Sub
    Private Sub rbXa_CheckedChanged(sender As Object, e As EventArgs) Handles rbXa.CheckedChanged
        If rbXa.Checked Then
            ComboBoxHuyen.Enabled = True
            ComboBoxXa.Enabled = True
        End If
    End Sub
    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    Private Sub ComboBoxHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxHuyen.SelectedIndexChanged
        cmd = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
End Class